package database;

import database.common.ConnectionFactory;
import model.Artikull;
import model.ArtikullKategori;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created with IntelliJ IDEA.
 * Author: Kostika Gorica
 * User:   kosta
 * Date:   3/10/13
 * Time:   10:33 PM
 */
public class ArtikullKategoriDao {

    private static final String INSERT_ARTIKULL_KATEGORI =
            "INSERT INTO public.kategori_artikull ( kodi, emri, pershkrimi, enabled) VALUES (?,?,?,?)";
    private static final String UPDATE_ARTIKULL_KATEGORI =
            "UPDATE public.kategori_artikull SET kodi=?, emri=?, pershkrimi=?, enabled=? WHERE id=? ";
    private static final String DELETE_ARTIKULL_KATEGORI =
            "DELETE FROM public.kategori_artikull WHERE id = ?";
    private static final String SELECT_SINGLE_KATEGORI =
            "SELECT id,  kodi, emri, pershkrimi, enabled FROM public.kategori_artikull WHERE id=?";
    private static final String SELECT_ALL_KATEGORI =
            "SELECT * from query_to_xml('SELECT * FROM public.kategori_artikull',true,false,'')";


    public static int insert(ArtikullKategori kategori){
        Connection connection = null;
        PreparedStatement statement = null;
        int rows = 0;
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement(INSERT_ARTIKULL_KATEGORI);
            statement.setString(1, kategori.getKodi());
            statement.setString(2, kategori.getEmri());
            statement.setString(3, kategori.getPershkrimi());
            statement.setInt(4, kategori.getEnabled());
            rows = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (statement!=null) statement.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return rows;
    }

    public static int update(ArtikullKategori kategori){
        Connection connection = null;
        PreparedStatement statement = null;
        int rows = 0;
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement(UPDATE_ARTIKULL_KATEGORI);
            statement.setString(1, kategori.getKodi());
            statement.setString(2, kategori.getEmri());
            statement.setString(3, kategori.getPershkrimi());
            statement.setInt(4, kategori.getEnabled());
            statement.setInt(5, kategori.getId());
            rows = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (statement!=null) statement.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return rows;
    }

    public static int delete(int id){
        Connection connection = null;
        PreparedStatement statement = null;
        int rows = 0;
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement(DELETE_ARTIKULL_KATEGORI);
            statement.setInt(1, id);
            rows = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (statement!=null) statement.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return rows;
    }

    public static ArtikullKategori selectArtikullKategori(int id){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        ArtikullKategori kategori = null;
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement(SELECT_SINGLE_KATEGORI);
            statement.setInt(1, id);
            resultSet = statement.executeQuery();
            if (resultSet.next()){
                kategori = new ArtikullKategori();
                kategori.setId(resultSet.getInt(1));
                kategori.setKodi(resultSet.getString(2));
                kategori.setEmri(resultSet.getString(3));
                kategori.setPershkrimi(resultSet.getString(4));
                kategori.setEnabled(resultSet.getInt(5));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (resultSet!=null) resultSet.close();
                if (statement!=null) statement.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return kategori;
    }

    public static String selectAllArtikullKategori(){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        String result = "";
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement(SELECT_ALL_KATEGORI);
            resultSet = statement.executeQuery();
            if (resultSet.next()){
                result=resultSet.getString(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (resultSet!=null) resultSet.close();
                if (statement!=null) statement.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return result;
    }

}
